﻿using HotelManager.Model;
using System.Data;
using System.Text;

namespace HotelManager.DAL
{
    public class RoomService : IRoomService
    {
        private readonly HotelDbContext _context;

        public RoomService(HotelDbContext context)
        {
            _context = context;
        }

        /// <summary>
        /// 获取房间信息
        /// </summary>
        /// <returns></returns>
        public List<RoomBusiness> GetRoomInfo(int roomId)
        {
            var list = _context.RoomBusinesses.Where(rb => roomId == 0 || rb.RoomId == roomId).ToList();
            //todo: 使用LINQ查询房间信息
            return list;

            StringBuilder sb = new StringBuilder("select a.RoomId, a.BedNum, b.RoomStateName, r.TypeName, a.Description from Room as a , RoomState as b, RoomType as r where a.RoomStateId=b.RoomStateId and a.RoomTypeId=r.TypeId");
            if (roomId != 0)
            {
                sb.AppendLine(" and a.RoomId=@roomId");
            }            
        }

        /// <summary>
        /// 删除房间信息
        /// </summary>
        /// <param name="roomId"></param>
        /// <returns></returns>
        public int DelRoomInfo(int roomId)
        {
            var room = _context.Rooms.Find(roomId);
            if (room == null)
            {
                return 0; // 房间不存在
            }
            _context.Rooms.Remove(room);
            return _context.SaveChanges();

            //string sql = "delete Room where RoomId=@roomId";
        }

        /// <summary>
        /// 添加房间信息
        /// </summary>
        /// <param name="room"></param>
        /// <returns></returns>
        public int AddRoomInfo(RoomBusiness room)
        {
            var newRoom = new Room
            {
                Description = room.Description,
                BedNum = room.BedNum,
                RoomTypeId = room.RoomTypeId,
                RoomStateId = room.RoomStateId
            };
            _context.Rooms.Add(newRoom);
            return _context.SaveChanges();
            //string sql = "insert into Room(RoomTypeId,RoomStateId,Description,BedNum) values(@roomTypeId,@roomStateId,@description,@bedNum)";
        }

        /// <summary>
        /// 更新房间信息
        /// </summary>
        /// <param name="room"></param>
        /// <returns></returns>
        public int UpdateRoomInfo(RoomBusiness room)
        {
            var existingRoom = _context.Rooms.Find(room.RoomId);
            if (existingRoom == null)
            {
                return 0; // 房间不存在
            }
            existingRoom.Description = room.Description;
            existingRoom.BedNum = room.BedNum;
            existingRoom.RoomTypeId = room.RoomTypeId;
            existingRoom.RoomStateId = room.RoomStateId;
            return _context.SaveChanges();
                
            //string sql = "update Room set Description=@description,BedNum=@bedNum,RoomTypeId=@roomTypeId,RoomStateId=@roomStateId where RoomId=@roomId";
        }

        /// <summary>
        /// 查询未入住房间
        /// </summary>
        /// <returns></returns>
        public List<Room> GetFreeRoom()
        {
            List<Room> room = new List<Room>();
            // todo: 使用LINQ查询未入住房间
            return room;

            //string sql = "select * from Room as r,RoomState as s where r.RoomStateId=s.RoomStateId and s.RoomStateName='空闲'";
        }


        /// <summary>
        /// 获取所有房间类型信息
        /// </summary>
        /// <returns></returns>
        public List<RoomType> GetAllRoomType()
        {
            return _context.RoomTypes.ToList();
            //string sql = "select * from RoomType";
        }

        /// <summary>
        /// 根据房间类型查询房间状态
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public List<Room> GetRoomStateByType(int typeId)
        {
            return _context.Rooms.Where(r => r.RoomTypeId == typeId).ToList();
            //string sql = "select * from Room where RoomTypeId=@roomTypeId";
        }

        /// <summary>
        /// 查询所有房间状态
        /// </summary>
        /// <returns></returns>
        public List<Room> GetRoomState()
        {
            return _context.Rooms.ToList();
            /**
            List<Room> room = new List<Room>();
            string sql = "select * from Room";
            SqlDataReader reader = SqlHelper.DataReader(sql);
            while (reader.Read())
            {
                Room r = new Room();
                r.RoomId = Convert.ToInt32(reader["RoomId"]);
                r.RoomStateId = Convert.ToInt32(reader["RoomStateId"]);
                room.Add(r);
            }
            reader.Close();
            return room;
            */
        }

        /// <summary>
        /// 查询总房间数
        /// </summary>
        /// <returns></returns>
        public int GetRoomCount(int typeId)
        {
            return _context.Rooms.Count();
            /**
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select count(1) from Room");
            if (typeId != 0)
            {
                sb.AppendLine(" where RoomTypeId=@roomTypeId");
            }
            SqlParameter[] para = { new SqlParameter("@roomTypeId", typeId) };
            return (int)SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text, para);
            **/
        }

        /// <summary>
        /// 根据状态ID和房间类型查询房间数
        /// </summary>
        /// <param name="stateId"></param>
        /// <returns></returns>
        public int GetRoomStateCount(int stateId, int typeId)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select count(1) from Room where RoomStateId=@roomStateId");
            if (typeId != 0)
            {
                sb.AppendLine(" and RoomTypeId=@roomTypeId");
            }
            return _context.Rooms
                .Where(r => r.RoomStateId == stateId && (typeId == 0 || r.RoomTypeId == typeId))
                .Count();

            //SqlParameter[] para = { new SqlParameter("roomStateId", stateId) ,
            //                        new SqlParameter("roomTypeId",typeId)};
            //return (int)SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text, para);
        }
    }
}
